package tt.dz.biz.dao.order;

import java.util.List;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.stereotype.Repository;
import tt.dz.biz.dao.BaseDAO;
import tt.dz.vo.OrderServiceVO;
import tt.dz.vo.UserBaseVO;

@Repository("orderServiceDAO")
public class OrderServiceDAOImpl
  extends BaseDAO
{
  private static final String LIST_ALL_ORDERSERVICE = "select  a.serviceNo,a.parentNo,a.name,a.isCanUse,a.isVirtual,a.virtualNo,a.creator,a.createTime  from order_service_info as a ";
  private static final String QUERY_UN_ALLOT_ORDERSERVICE = "select a.serviceNo,a.name from order_service_info a where a.isvalid = 1 and a.parentno = '#' and a.isvirtual = 0 except (select a.serviceNo,a.name from ms_user_service b  LEFT JOIN order_service_info a on b.service_no = a.serviceno where b.user_id = :userId and b.is_valid = 1 )";
  private static final String QUERY_SERVICE_BY_CODE = "select a.serviceNo,a.parentNo,a.name,a.virtualNo,a.isVirtual,a.isCanUse FROM order_service_info a  where a.serviceNo=:serviceNo";
  private static final String QUERY_SERVICE_BY_PARENTCODE = "select a.serviceNo,a.parentNo,a.name,a.virtualNo,a.isVirtual,a.isCanUse FROM order_service_info a  where a.parentNo=:parentNo";
  private static final String LIST_ALL_COST_ORDERSERVICE = "SELECT DISTINCT osi.serviceNo,osi.parentNo,osi.name from order_service_provider  osp , ms_merchant mm, order_service_info osi  where osp.serviceprovidercode = mm.merchant_code and osp.serviceno = osi.serviceno and mm.is_self = 1 and osp.iscost = 1";
  
  public List<OrderServiceVO> listAllOrderService()
  {
    StringBuilder sql = new StringBuilder("select  a.serviceNo,a.parentNo,a.name,a.isCanUse,a.isVirtual,a.virtualNo,a.creator,a.createTime  from order_service_info as a ");
    sql.append("  where  a.isvalid = 1 ");
    return queryForList(sql.toString(), null, OrderServiceVO.class);
  }
  
  public List<OrderServiceVO> listAllCostOrderService()
  {
    return queryForList("SELECT DISTINCT osi.serviceNo,osi.parentNo,osi.name from order_service_provider  osp , ms_merchant mm, order_service_info osi  where osp.serviceprovidercode = mm.merchant_code and osp.serviceno = osi.serviceno and mm.is_self = 1 and osp.iscost = 1", null, OrderServiceVO.class);
  }
  
  public List<OrderServiceVO> queryUnAllotOrderService(UserBaseVO userBaseVO)
  {
    return queryForList("select a.serviceNo,a.name from order_service_info a where a.isvalid = 1 and a.parentno = '#' and a.isvirtual = 0 except (select a.serviceNo,a.name from ms_user_service b  LEFT JOIN order_service_info a on b.service_no = a.serviceno where b.user_id = :userId and b.is_valid = 1 )", userBaseVO, OrderServiceVO.class);
  }
  
  public OrderServiceVO queryServiceByCode(String serviceNo)
  {
    try
    {
      OrderServiceVO o = new OrderServiceVO();
      o.setServiceNo(serviceNo);
      return (OrderServiceVO)queryForObject("select a.serviceNo,a.parentNo,a.name,a.virtualNo,a.isVirtual,a.isCanUse FROM order_service_info a  where a.serviceNo=:serviceNo", o, OrderServiceVO.class);
    }
    catch (EmptyResultDataAccessException e) {}
    return null;
  }
  
  public List<OrderServiceVO> queryServiceByParentCode(String parentNo)
  {
    OrderServiceVO o = new OrderServiceVO();
    o.setParentNo(parentNo);
    return queryForList("select a.serviceNo,a.parentNo,a.name,a.virtualNo,a.isVirtual,a.isCanUse FROM order_service_info a  where a.parentNo=:parentNo", o, OrderServiceVO.class);
  }
  
  public List<OrderServiceVO> queryDefaultService()
  {
    StringBuilder sql = new StringBuilder("select  a.serviceNo,a.parentNo,a.name,a.isCanUse,a.isVirtual,a.virtualNo,a.creator,a.createTime  from order_service_info as a ");
    sql.append("  where  a.iscanuse = 1 and a.isvalid = 1 and a.isVirtual=0 and a.parentno='#'");
    return queryForList(sql.toString(), null, OrderServiceVO.class);
  }
}
